﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Globalization;
using System.Data;

namespace TravelexForwardContracts
{
    class XDBOp
    {
        /* ************************************************
         * Data Member
         * ************************************************/
        private String m_strConn;

        /* ************************************************
         * Public Function
         * ************************************************/
        public XDBOp()
        {
            Properties.Settings config = Properties.Settings.Default;
            m_strConn = config.TravelexForwardContractConnectionString;
            if (m_strConn == "")
            {
                throw new Exception("[NG] Connect Database Failed. Application will close");
            }
        }

        /* *************************************************
         * Public Function
         * *************************************************/
        public DataSetCompanyRecords findCompanyRecords(String in_clinetId)
        {
            DataSetCompanyRecords ds = new DataSetCompanyRecords();
            String sql = "SELECT * FROM TCompanyRecords WHERE ClientId='" +
                in_clinetId + "'";

            try
            {
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, m_strConn))
                {
                    dataAdapter.Fill(ds, "TCompanyRecords");
                }
            }
            catch (System.Exception ex)
            {
                using (HMessageBox hMsg = new HMessageBox())
                {
                    hMsg.logMsg("[NG]:findCompanyRecords", ex.Message);
                }
            }

            return ds;
        }


//         private void execPFindExistRecords(DataRow in_dataRow)
//         {
//             try
//             {
//                 using (SqlConnection sqlConn = new SqlConnection(m_strConn))
//                 {
//                     sqlConn.Open();
//                     SqlCommand sqlCmd = sqlConn.CreateCommand();
//                     sqlCmd.CommandType = CommandType.StoredProcedure;
//                     sqlCmd.CommandText = "h_sp_find_exist_records";
// 
//                     //Create input parameter
//                     SqlParameter logicalNumber = sqlCmd.Parameters.Add("@in_logicalNumber", SqlDbType.VarChar);
//                     logicalNumber.Direction = ParameterDirection.Input;
//                     logicalNumber.Value = in_dataRow[0].ToString().Trim();
// 
//                     SqlParameter name = sqlCmd.Parameters.Add("@in_name", SqlDbType.VarChar);
//                     name.Direction = ParameterDirection.Input;
//                     name.Value = in_dataRow[1].ToString().Trim();
// 
//                     SqlParameter counterparty = sqlCmd.Parameters.Add("@in_counterparty", SqlDbType.VarChar);
//                     counterparty.Direction = ParameterDirection.Input;
//                     counterparty.Value = in_dataRow[2].ToString().Trim();
// 
//                     SqlParameter maturityDate = sqlCmd.Parameters.Add("@in_maturityDate", SqlDbType.VarChar);
//                     maturityDate.Direction = ParameterDirection.Input;
//                     maturityDate.Value = Convert.ToDateTime(in_dataRow[3].ToString()).ToShortDateString();
// 
//                     SqlParameter totAmount = sqlCmd.Parameters.Add("@in_totAmount", SqlDbType.VarChar);
//                     totAmount.Direction = ParameterDirection.Input;
//                     totAmount.Value = in_dataRow[4].ToString().Trim();
// 
//                     SqlParameter paidStatus = sqlCmd.Parameters.Add("@in_paidSatus", SqlDbType.VarChar);
//                     paidStatus.Direction = ParameterDirection.Input;
//                     paidStatus.Value = in_dataRow[5].ToString().Trim();
// 
//                     SqlParameter localccy = sqlCmd.Parameters.Add("@in_localccy", SqlDbType.VarChar);
//                     localccy.Direction = ParameterDirection.Input;
//                     localccy.Value = in_dataRow[6].ToString().Trim();
// 
//                     SqlParameter localAmount = sqlCmd.Parameters.Add("@in_localAmount", SqlDbType.VarChar);
//                     localccy.Direction = ParameterDirection.Input;
//                     localccy.Value = in_dataRow[7].ToString().Trim();
// 
//                     SqlParameter rate = sqlCmd.Parameters.Add("@in_rate", SqlDbType.VarChar);
//                     rate.Direction = ParameterDirection.Input;
//                     rate.Value = in_dataRow[8].ToString().Trim();
// 
//                     SqlParameter currency = sqlCmd.Parameters.Add("@in_currency", SqlDbType.VarChar);
//                     currency.Direction = ParameterDirection.Input;
//                     currency.Value = in_dataRow[9].ToString().Trim();
// 
//                     SqlParameter instrument = sqlCmd.Parameters.Add("@in_instrument", SqlDbType.VarChar);
//                     instrument.Direction = ParameterDirection.Input;
//                     instrument.Value = in_dataRow[10].ToString().Trim();
//                 }
//             }
//             catch (System.Exception ex)
//             {
//                 throw new Exception("XDbOp: " + ex.Message);
//             }
//         }
// 
        /*!
         * @brief : Search in DataBase:TPayment
         *          Find Out the records with previousDate
         */
        public DataSetPayment findNewContracts(DataSetPayment in_dsPayment, DateTime in_previousDate)
        {
            String xlsCounterparty, xlsUpdateDate, xlsMaturityDate, xlsTotAmount,dbUpdateDate;
            dbUpdateDate = DateTime.Parse(in_previousDate.ToShortDateString()).ToString("yyyy-MM-dd");
            DataSetPayment dsNewRecords = new DataSetPayment();

            foreach (DataRow row in in_dsPayment.Tables[0].Rows)
            {
                xlsCounterparty = row["Counterparty"].ToString().Trim();
                xlsUpdateDate = row["UpdateDate"].ToString().Trim();
                xlsMaturityDate = row["MaturityDate"].ToString().Trim();
                xlsTotAmount = row["TotAmount"].ToString().Trim();
                String dbMaturityDate = (Convert.ToDateTime(xlsMaturityDate)).ToString("yyyy-MM-dd");
                
                String sqlQueryUpdateDate = "SELECT * FROM TPayment WHERE " +
                    "Counterparty='" + xlsCounterparty + "' AND " +
                    "MaturityDate='" + dbMaturityDate + "' AND " +
                    "UpdateDate='" + dbUpdateDate + "' AND " +
                    "TotAmount='" + xlsTotAmount + "'";

                using (SqlConnection sqlConn = new SqlConnection(m_strConn))
                {
                    try
                    {
                        sqlConn.Open();
                        using (SqlCommand sqlCmd = new SqlCommand(sqlQueryUpdateDate, sqlConn))
                        {
                            SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                            if (!sqlReader.Read())
                            {   // If cannot find, means there is no record in previous date.
                                // then the row in excel is new records
                                DataRow newRow = dsNewRecords.Tables[0].NewRow();
                                for (int i = 0; i < in_dsPayment.Tables[0].Columns.Count;i++ )
                                {
                                    newRow[i] = row[i]; 
                                }
                                dsNewRecords.Tables[0].Rows.Add(newRow);
                                dsNewRecords.AcceptChanges();
                               
                            }

                            sqlReader.Close();
                        }
                        sqlConn.Close();
                    }
                    catch (System.Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
            
            return dsNewRecords;
        }

        /*
         * @!brief : Drop Out Contracts: yesterday have, today don't have
         *           Get all rows with previous date in Db, and set as dbDs.
         *           compare each row in dbDs with all xls rows,
         *           if find match "MaturityDate", "Counterparty","TotAmount", it's not drop out
         *           otherwise, it's drop out project.
         */
        public DataSetPayment findDropOutContracts(DataSetPayment in_dsPayment, DateTime in_previousDate)
        {
            DataSetPayment dsDropOutRecords = new DataSetPayment();
            String dbUpdateDate = DateTime.Parse(in_previousDate.ToShortDateString()).ToString("yyyy-MM-dd");

            String sqlPreviousDateContracts = "SELECT * FROM TPayment WHERE UpdateDate='" + dbUpdateDate + "'";

            try
            {
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlPreviousDateContracts, m_strConn))
                {
                    DataSet dsFromDb = new DataSet();
                    dataAdapter.Fill(dsFromDb,"TPayment");
                    if (dsFromDb.Tables[0].Rows.Count < 1)
                    {
                        return null;
                    }

                    foreach (DataRow dbRow in dsFromDb.Tables[0].Rows)
                    {
                        bool isFind = false;
                        foreach (DataRow xlsRow in in_dsPayment.Tables[0].Rows)
                        {
                            if(0==String.Compare(dbRow["Counterparty"].ToString().Trim(), xlsRow["Counterparty"].ToString().Trim(),true))
                            {
                                if (0 == String.Compare(dbRow["MaturityDate"].ToString().Trim(), xlsRow["MaturityDate"].ToString().Trim(), true))
                                {
                                    if(0 == String.Compare(dbRow["TotAmount"].ToString().Trim(),xlsRow["TotAmount"].ToString().Trim(),true))
                                    {
                                        isFind = true;
                                        break;
                                    }
                                }
                            }//End-Of-XLS-Row
                        }
                        if (isFind==false)
                        {
                            dsDropOutRecords.Tables[0].Rows.Add(dbRow);
                        }
                    }//End-Of-DbRow
                }
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return dsDropOutRecords;
        }

        public void updatePayment(DataSetPayment in_dsPayment)
        {
            if (in_dsPayment == null || in_dsPayment.Tables[0].Rows.Count < 1)
            {
                return;
            }

            try
            {
                SqlConnection sqlConn = new SqlConnection(m_strConn);
                SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);

                bulkCopy.DestinationTableName = in_dsPayment.Tables[0].TableName;

                sqlConn.Open();
                if (in_dsPayment.Tables[0] != null && in_dsPayment.Tables[0].Rows.Count > 0)
                {
                    bulkCopy.WriteToServer(in_dsPayment.Tables[0]);
                }

                if (bulkCopy != null)
                {
                    bulkCopy.Close();
                }

                sqlConn.Close();
            }
            catch (System.Exception ex)
            {
                using (HMessageBox HMsg = new HMessageBox())
                {
                    HMsg.logMsg("Update Paid & Unpaid into Database failed", ex.Message);
                }
            }

        }

        public void updateAllFEC(DataSetAllFEC in_dsAllFEC)
        {
            if (in_dsAllFEC == null || in_dsAllFEC.Tables[0].Rows.Count < 1)
            {
                return;
            }

            try
            {
                SqlConnection sqlConn = new SqlConnection(m_strConn);
                SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);

                bulkCopy.DestinationTableName = in_dsAllFEC.Tables[0].TableName;

                sqlConn.Open();
                if (in_dsAllFEC.Tables[0] != null && in_dsAllFEC.Tables[0].Rows.Count > 0)
                {
                    bulkCopy.WriteToServer(in_dsAllFEC.Tables[0]);
                }

                if (bulkCopy != null)
                {
                    bulkCopy.Close();
                }

                sqlConn.Close();
            }
            catch (System.Exception ex)
            {
                using (HMessageBox HMsg = new HMessageBox())
                {
                    HMsg.logMsg("Update All FEC into Database failed", ex.Message);
                }

            }
        }
    }
}